package com.java.servlet.web.roles.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.java.servlet.web.commons.GenericDao;
import com.java.servlet.web.conexion.PoolConnection;
import com.java.servlet.web.roles.dao.sql.SqlRoles;
import com.java.servlet.web.roles.vo.RolesVo;

public class RolesDaoImpl extends GenericDao implements RolesDao {
	private static final Logger log = Logger.getLogger(RolesDaoImpl.class);
	private Connection conn = null;

	public RolesDaoImpl() {
		conn = PoolConnection.getInstance().getConection();
	}

	@Override
	public List<RolesVo> getListaRoles(RolesVo rolesVo) {
		int index = 1;
		PreparedStatement stm = null;
		ResultSet rs = null;
		List<RolesVo> listaRoles = null;
		try {
			if (rolesVo.getIdRol() != 0) {
				stm = conn.prepareStatement(SqlRoles.DATOS_ROLES + SqlRoles.DATOS_ROLES_IDROL);
				stm.setInt(index++, rolesVo.getIdRol());
				log.info("LISTA_ROLES  [ " + SqlRoles.DATOS_ROLES + SqlRoles.DATOS_ROLES_IDROL);
			} else{
				stm = conn.prepareStatement(SqlRoles.DATOS_ROLES);
				log.info("LISTA_ROLES  [ " + SqlRoles.DATOS_ROLES);				
			}
			rs = stm.executeQuery();
			listaRoles = getListaRolesResultSet(rs);
		} catch (SQLException ex) {
			log.error("getMenuUsuario  [ " + ex);
			PoolConnection.getInstance().closeConnection(conn);
		} finally {
			 try {
	                rs.close();
	                stm.close();
	            } catch (SQLException e) {
	            	log.error("SQLException  [ " + e);
	            } 
		}
		return listaRoles;
	}

	private List<RolesVo> getListaRolesResultSet(ResultSet rs) {
		List<RolesVo> listaRoles = null;
		try{
			if(rs.next()){
				listaRoles = new ArrayList<RolesVo>();
				do{
					RolesVo rolesVo = new RolesVo();
					rolesVo.setIdRol(rs.getInt("ID_ROL"));
					rolesVo.setDescRol(rs.getString("DESC_ROL"));
					rolesVo.setIdVigencia(rs.getString("VIGENCIA"));
					rolesVo.setDescVigencia(rs.getString("DESC_VIGENCIA"));

					listaRoles.add(rolesVo);
				}while(rs.next());
			}
		}catch(SQLException ex){
			log.error("getListaRolesResultSet   [  " + ex);
		}
		return listaRoles;
	}

	@Override
	public RolesVo getRol(RolesVo rolesVo) {
		int index = 1;
		PreparedStatement stm = null;
		ResultSet rs = null;
		RolesVo role = null;
		try {
				stm = conn.prepareStatement(SqlRoles.DATOS_ROLES + SqlRoles.DATOS_ROLES_IDROL);
				stm.setInt(index++, rolesVo.getIdRol());
				log.info("ROL  [ " + SqlRoles.DATOS_ROLES + SqlRoles.DATOS_ROLES_IDROL);
			rs = stm.executeQuery();
			role = getRoleResultSet(rs);
		} catch (SQLException ex) {
			log.error("getRol  [ " + ex);
			PoolConnection.getInstance().closeConnection(conn);
		} finally {
			 try {
	                rs.close();
	                stm.close();
	            } catch (SQLException e) {
	            	log.error("SQLException  [ " + e);
	            } 
		}
		return role;
	}
	private RolesVo getRoleResultSet(ResultSet rs) {
		RolesVo rolVo = null;
		try{
			if(rs.next()){
				do{
					rolVo = new RolesVo();
					rolVo.setIdRol(rs.getInt("ID_ROL"));
					rolVo.setDescRol(rs.getString("DESC_ROL"));
					rolVo.setIdVigencia(rs.getString("VIGENCIA"));
					rolVo.setDescVigencia(rs.getString("DESC_VIGENCIA"));

				}while(rs.next());
			}
		}catch(SQLException ex){
			log.error("getRoleResultSet   [  " + ex);
		}
		return rolVo;
	}

	@Override
	public void guardarNuevoRol(RolesVo rolesVo) {
		int index = 1;
		PreparedStatement stm = null;
		try {
			stm = conn.prepareStatement(SqlRoles.SAVE_NUEVO_ROL);
			stm.setInt(index++, consecutivoRol());
			stm.setString(index++, rolesVo.getDescRol());
			stm.setString(index++, rolesVo.getIdVigencia());
			log.info("SAVE_NUEVO_ROL  [ " + SqlRoles.SAVE_NUEVO_ROL);		
			stm.executeUpdate();
		} catch(SQLException ex){
			log.error("Error al guardarNuevoRol el rol [ " + ex);
		} finally {
			 try {
	                stm.close();
	            } catch (SQLException e) {
	            	log.error("SQLException  [ " + e);
	            } 
        }
	}
	
    public int consecutivoRol() {
    	int consecutivo = 0;
        PreparedStatement stm = null;
        ResultSet rs = null;
         
        try {
            stm = conn.prepareStatement(SqlRoles.MAX_ID_ROL);
            log.info("MAX_ID_ROL  [ " + SqlRoles.MAX_ID_ROL);
            rs = stm.executeQuery();
            if(rs.next()) {
            	consecutivo = rs.getInt(1);
            }
        } catch (SQLException ex) {
            log.error("Error en consecutivoRol [ ", ex);
        } finally {
			 try {
	                stm.close();
	            } catch (SQLException e) {
	            	log.error("SQLException  [ " + e);
	            } 
        }
        return consecutivo;
    }

	@Override
	public void guardarEditRol(RolesVo rolesVo) {
		int index = 1;
		PreparedStatement stm = null;
		try {
			stm = conn.prepareStatement(SqlRoles.SAVE_EDIT_ROL);
			stm.setString(index++, rolesVo.getDescRol());
			stm.setString(index++, rolesVo.getIdVigencia());
			stm.setInt(index++, rolesVo.getIdRol());
			log.info("SAVE_EDIT_ROL  [ " + SqlRoles.SAVE_EDIT_ROL);
			stm.executeUpdate();
		} catch(SQLException ex){
			log.error("Error al guardarEditRol el rol [ " + ex);
		} finally {
			 try {
	                stm.close();
	            } catch (SQLException e) {
	            	log.error("SQLException  [ " + e);
	            } 
        }
	}

	@Override
	public void borrarRol(RolesVo rolesVo) {
		int index = 1;
		PreparedStatement stm = null;
		try {
			stm = conn.prepareStatement(SqlRoles.DELETE_ROL);
			stm.setInt(index++, rolesVo.getIdRol());
			log.info("DELETE_ROL  [ " + SqlRoles.DELETE_ROL);
			stm.executeUpdate();
		} catch(SQLException ex){
			log.error("Error al borrarRol el rol [ " + ex);
		} finally {
			 try {
	                stm.close();
	            } catch (SQLException e) {
	            	log.error("SQLException  [ " + e);
	            } 
        }
	}
}
